﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using NPOI.HSSF.Model;
using NPOI.POIFS.FileSystem;
using System.IO;
using System.Data;
using NPOI.HSSF.Model; // InternalWorkbook
using NPOI.HSSF.UserModel; // HSSFWorkbook, HSSFSheet
public partial class _Default : System.Web.UI.Page
{
    string filePath = "Book1.xlsx";
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        filePath = Server.MapPath(filePath);
        using (var pck = new OfficeOpenXml.ExcelPackage())
        {
            using (var stream = File.OpenRead(filePath))
            {
                pck.Load(stream);
            }

            String wSheet = "";
            DataSet dataset = new DataSet();
            foreach (var ws1 in pck.Workbook.Worksheets)
            {
                DataTable dt = ReadExcelSheet(ws1);
                dataset.Tables.Add(dt);
            }
            Response.Write(wSheet);
        }

    }
    private DataTable ReadExcelSheet(ExcelWorksheet sheet)
    {
        try
        {
            DataTable tbl = new DataTable(sheet.Name);
            bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
            foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
            {
                tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= sheet.Dimension.End.Row; rowNum++)
            {
                var wsRow = sheet.Cells[rowNum, 1, rowNum, sheet.Dimension.End.Column];
                var row = tbl.NewRow();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
                tbl.Rows.Add(row);
            }
            return tbl;
        }
        catch (Exception e)
        {
            throw new Exception("Can not read excel sheet: " + sheet.Name);
        }
    }

    public DataTable getTest()
    {
        DataTable dt = new DataTable();
        String[] rowheader = new String[] { "Type", "Code", "Desc", "Note" };
        dt.Columns.Add(new DataColumn("Type"));
        dt.Columns.Add(new DataColumn("Code"));
        dt.Columns.Add(new DataColumn("Desc"));
        dt.Columns.Add(new DataColumn("Note"));
        String[] row = null;
        for (int i = 0; i < 100; i++)
        {
            if (i % 2 == 0)
            {
                row = new String[] { "Type 2", "Code" + i, "Desc" + i, "Note" + i };
                dt.Rows.Add(row);
                continue;
            }
            if (i % 5 == 0)
            {
                row = new String[] { "Type 5", "Code" + i, "Desc" + i, "Note" + i };
                dt.Rows.Add(row);
                continue;
            }
            if (i % 7 == 0)
            {
                row = new String[] { "Type 7", "Code" + i, "Desc" + i, "Note" + i };
                dt.Rows.Add(row);
                continue;
            }
            if (i % 9 == 0)
            {
                row = new String[] { "Type 9", "Code" + i, "Desc" + i, "Note" + i };
                dt.Rows.Add(row);
                continue;
            }
        }
        return dt;
    }


    protected void Button2_Click(object sender, EventArgs e)
    {
        DataTable dt = getTest();



        DataView view = new DataView(dt);
        DataTable distinctValues = view.ToTable(true, "Type");
        DataTable[] dtarr = new DataTable[distinctValues.Rows.Count];
        for (int i = 0; i <= distinctValues.Rows.Count - 1; i++)
        {

            DataTable dtTemp = dt.AsEnumerable().Where(r => r.Field<String>("Type") == distinctValues.Rows[i][0].ToString())
            .CopyToDataTable();
            dtTemp.TableName = distinctValues.Rows[i][0].ToString();
            dtarr[i] = dtTemp;
        }

        Response.WriteFile(Write(dtarr));
    }

    public String  Write(DataTable[] listDataTable)
    {
        HSSFWorkbook wb;
        HSSFSheet sh;
        String file = "E:\\test" + DateTime.Now.Ticks + ".xls";
        if (!File.Exists(file))
        {
            wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());

            foreach (DataTable tb in listDataTable)
            {
                // create sheet
                sh = (HSSFSheet)wb.CreateSheet(tb.TableName);

                // create sheet header
                var r = sh.CreateRow(0);
                for (int j = 0; j < tb.Columns.Count - 1; j++)
                {
                    r.CreateCell(j);
                    sh.GetRow(0).GetCell(j).SetCellValue(tb.Columns[j + 1].ColumnName);
                   
                }

                // create excel content
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    DataRow row = tb.Rows[i];
                    r = sh.CreateRow(i+1);
                    for (int j = 0; j < tb.Columns.Count - 1; j++)
                    {
                        r.CreateCell(j);
                        sh.GetRow(i+1).GetCell(j).SetCellValue((String)row[j + 1]);
                    }
                }
            }

            using (var fs = new FileStream(file, FileMode.Append, FileAccess.Write))
            {
                wb.Write(fs);
            }
        }
        return file;
    }
}